package com.hxwm.hwdbtool.util;

import com.hxwm.hwdbtool.entity.dbconnect.*;
import javafx.scene.control.TreeItem;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @program: hwdbtool
 * @ClassName DataSourceMeta
 * @description:
 * @author: hxwm
 * @create: 2024-06-20 22:37
 * @Version 1.0
 **/
public class DataSourceMetaDataUtil {

    /**
     * 查询数据库导航栏列表
     * @param hwDateSource
     * @return
     * @throws SQLException
     */
    public static HwConnection getDatabaseConnectInfo(HwDateSource hwDateSource) {
        //数据源
        HwConnection hwConnection = new HwConnection();
        try {
            String dbIp = hwDateSource.getDbIp();
            String dbUrl = hwDateSource.getDbUrl();
            DatabaseMetaData metaData = hwDateSource.getConnection().getMetaData();
            hwConnection.setConnectionName(dbIp + "-" + dbUrl);
            if("mysql".equals(hwDateSource.getDriverType())) {
                hwConnection.setDatabaseList(getDatabaseNames(metaData));
                hwConnection.setUserList(getDatabaseUserNames(hwDateSource));
                hwConnection.setHwDateSource(hwDateSource);
            }
            if("db2".equals(hwDateSource.getDriverType())){
                List<HwSchema> schemaNames = DataSourceMetaDataUtil.getSchemaNames(hwDateSource);
                hwConnection.setSchemaList(schemaNames);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return hwConnection;
    }


    public static List<HwSchema> getSchemaNames(HwDateSource hwDateSource) {
        List<HwSchema> schemaList = new ArrayList<>();
        try (Connection conn = hwDateSource.getConnection(); Statement stmt = conn.createStatement()) {
            // 查询模式
            ResultSet schemaResult = stmt.executeQuery("SELECT SCHEMANAME FROM SYSCAT.SCHEMATA");
            while(schemaResult.next()){
                String schemaName = schemaResult.getString("SCHEMANAME");
                HwSchema hwSchema = new HwSchema();
                List<HwTable> hwTableList = new ArrayList<>();
                List<HwView> hwViewList = new ArrayList<>();
                List<HwIndex> hwIndexList = new ArrayList<>();

                hwSchema.setSchemaName(schemaName);
                ResultSet tableResult = stmt.executeQuery("SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES");
                while(tableResult.next()){
                    HwTable table = new HwTable();
                    String tableName = tableResult.getString("TABNAME");
                    table.setTableName(tableName);
                    hwTableList.add(table);
                }
                ResultSet viewResult = stmt.executeQuery("SELECT VIEWSCHEMA, VIEWNAME FROM SYSCAT.VIEWS");
                while(viewResult.next()){
                    HwView view = new HwView();
                    String viewName = viewResult.getString("VIEWNAME");
                    view.setViewName(viewName);
                    hwViewList.add(view);
                }

                ResultSet indexResult = stmt.executeQuery("SELECT VIEWSCHEMA, VIEWNAME FROM SYSCAT.VIEWS");
                while(indexResult.next()){
                    HwIndex index = new HwIndex();
                    String indexName = indexResult.getString("INDNAME");
                    index.setIndexName(indexName);
                    hwIndexList.add(index);
                }

                hwSchema.setTableList(hwTableList);
                hwSchema.setViewList(hwViewList);
            }

        }catch (Exception e){

        }

        return schemaList;
    }
    /**
     * 获取数据库连接的数据库名列表
     * @param metaData
     * @return
     * @throws SQLException
     */
    public static List<HwDatabase> getDatabaseNames(DatabaseMetaData metaData) throws SQLException {
        List<HwDatabase> databaseList = new ArrayList<>();

        ResultSet catalogs = metaData.getCatalogs();
        while (catalogs.next()){
            HwDatabase hwDatabase = new HwDatabase();
            String databaseName = catalogs.getString("TABLE_CAT");
            hwDatabase.setDatabaseName(databaseName);

            List<HwTable> tableList = getTableNames(metaData,databaseName);
            List<HwView> viewList = getViewNames(metaData,databaseName);
            hwDatabase.setTableList(tableList);
            hwDatabase.setViewList(viewList);
            databaseList.add(hwDatabase);
        }
        return databaseList;
    }

    /**
     * 获取数据库下的表名称列表
     * @param metaData
     * @param databaseName
     * @return
     * @throws SQLException
     */
    public static List<HwTable> getTableNames(DatabaseMetaData metaData,String databaseName) throws SQLException {
        List<HwTable> tableList = new ArrayList<>();
        ResultSet tables = metaData.getTables(databaseName, null, null, new String[]{"TABLE"});
        while (tables.next()){
            HwTable hwTable = new HwTable();
            String tableName = tables.getString("TABLE_NAME");
            hwTable.setTableName(tableName);

            List<HwView> viewList = getViewNames(metaData,tableName);
            List<HwColumn> columnList = getColumnNames(metaData,tableName);
            List<HwPrimaryKey> primaryKeyList = new ArrayList<>();
            List<HwForeignKey> foreignKeyList = new ArrayList<>();
            List<HwQuote> quoteList = new ArrayList<>();
            List<HwTrigger> triggerList = new ArrayList<>();
            List<HwIndex> indexList = new ArrayList<>();

            hwTable.setViewList(viewList);
            hwTable.setColumnList(columnList);
            hwTable.setPrimaryKeyList(primaryKeyList);
            hwTable.setForeignKeyList(foreignKeyList);
            hwTable.setQuoteList(quoteList);
            hwTable.setTriggerList(triggerList);
            hwTable.setIndexList(indexList);
            tableList.add(hwTable);
        }
        return tableList;
    }

    /**
     * 查询视图
     * @param metaData
     * @param databaseName
     * @return
     * @throws SQLException
     */
    public static List<HwView> getViewNames(DatabaseMetaData metaData,String databaseName) throws SQLException {
        List<HwView> viewList = new ArrayList<>();
        ResultSet views = metaData.getTables(databaseName, null, "%", new String[]{"VIEW"});
        while(views.next()){
            HwView hwView = new HwView();
            String viewName = views.getString("TABLE_NAME");
            hwView.setViewName(viewName);
            hwView.setColumnList(getColumnNames(metaData,viewName));
            viewList.add(hwView);
        }
        return viewList;
    }

    /**
     * 获取数据库表字段列表
     * @param metaData
     * @param tableName
     * @return
     * @throws SQLException
     */
    public static List<HwColumn> getColumnNames(DatabaseMetaData metaData,String tableName) throws SQLException {
        List<HwColumn> columnList = new ArrayList<>();
        ResultSet columns = metaData.getColumns(null, null, tableName, null);
        while (columns.next()){
            HwColumn hwColumn = new HwColumn();
            String columnName = columns.getString("COLUMN_NAME");
            String typeName = columns.getString("TYPE_NAME");
            String columnSize = columns.getString("COLUMN_SIZE");

            hwColumn.setColumnName(columnName);
            hwColumn.setColumnType(typeName);
            hwColumn.setColumnSize(columnSize);

            columnList.add(hwColumn);
        }
        return columnList;
    }

    /**
     * 查询数据库用户信息
     * @param hwDateSource
     * @return
     */
    public static List<HwDBUser> getDatabaseUserNames(HwDateSource hwDateSource){
        List<HwDBUser> userList = new ArrayList<>();
        try (Statement statement = hwDateSource.getConnection().createStatement()){
            //查询用户列表
            String driverType = hwDateSource.getDriverType();
            String queryUserInfoSql = "";
            switch (driverType){
                case "mysql":
                    queryUserInfoSql = "SELECT User as username,Host as host  FROM mysql.user;";
                    break;
                case "db2":
                    queryUserInfoSql = "SELECT DISTINCT GRANTEE as username FROM SYSIBMADM.AUTHORIZATIONIDS;";
                    break;
                case "oracle":
                    queryUserInfoSql = "SELECT username FROM all_users;";
                    break;
                case "informix-sqli":
                    queryUserInfoSql = "SELECT username FROM sysusers;";
                    break;
                case "sqlserver":
                    queryUserInfoSql = "SELECT name as username FROM sys.server_principals WHERE type_desc = 'SQL_LOGIN';";
                    break;
                default:
                    break;
            }
            ResultSet resultSet = statement.executeQuery(queryUserInfoSql);
            while (resultSet.next()){
                HwDBUser hwDBUser = new HwDBUser();
                String username = resultSet.getString("username");
                String host = resultSet.getString("host");

                hwDBUser.setUserName(username);
                hwDBUser.setUserRightList(getUserRights(hwDateSource,username,host));

                userList.add(hwDBUser);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return userList;
    }
    public static List<HwUserRight> getUserRights(HwDateSource hwDateSource,String username,String host) {
        //查询用户权限
        List<HwUserRight> userRightList = new ArrayList<>();
        try (Statement statement = hwDateSource.getConnection().createStatement()) {
            String queryPermissions = "";
            String driverType = hwDateSource.getDriverType();
            switch (driverType) {
                case "mysql":
                    queryPermissions = "SHOW GRANTS FOR '" + username + "'@'" + host + "'";
                    break;
                case "db2":
                    queryPermissions = "SELECT DISTINCT GRANTEE as username FROM SYSIBMADM.AUTHORIZATIONIDS;";
                    break;
                case "oracle":
                    queryPermissions = "SELECT username FROM all_users;";
                    break;
                case "informix-sqli":
                    queryPermissions = "SELECT username FROM sysusers;";
                    break;
                case "sqlserver":
                    queryPermissions = "SELECT name as username FROM sys.server_principals WHERE type_desc = 'SQL_LOGIN';";
                    break;
                default:
                    break;
            }
            ResultSet resultSet = statement.executeQuery(queryPermissions);
            while (resultSet.next()) {
                HwUserRight hwUserRight = new HwUserRight();
                String permission = resultSet.getString(1);
                hwUserRight.setUserRightName(permission);
                userRightList.add(hwUserRight);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return userRightList;
    }

}